from urllib.request import urlopen
import sqlite3
import re


'''
TODO list (in order of importance):
- ENG seminars: different sections have different subtitles, but the page
  is formatted like a regular table (instead of like the first-year seminars).
  Need custom code to get at it! Subtitles are italic.
- Remove print() statements that are there for debugging.
- Code that looks for bold text and updates db with that?? (Probably too much
  work for too little gain. Re-parsing is speedy.)
- test cases for rows surrounding cancelled sections:
  es (only section cancelled),
  psy (first section cancelled, following sections ok),
  csc (basic)
'''


# --- Regex components ---
# Since these patterns are used more than once, it's more efficient to compile
# them just once here and reuse them.

# Matches any number of html tags. Greedy.
greedy_html_tag_p = '(?:<.*?>)*'

# Matches any number of html tags. Non-greedy.
nongreedy_html_tag_p = '(?:<.*?>)*?'

# Matches whitespace but result not retrievable.
whitespace_p = '(?:\s*?)'

# Matches contents of a table row.
# <tr...> { matched contents } </tr>
row_p = whitespace_p + '<tr.*?>' + whitespace_p + \
    '(.*?)' + whitespace_p + '</tr>' + whitespace_p

# Matches contents of a table cell. Contents may be surrounded by other tags.
# Like using td_p pattern, then using tag_contents_p pattern on its result.
# <td...>(<?>, ...) { matched contents } (<?>, possibly other plain txt) </td>
cell_p = whitespace_p + '<td.*?>' + whitespace_p + \
    greedy_html_tag_p + '([^<]*)' + nongreedy_html_tag_p + \
    whitespace_p + '</td>' + whitespace_p

# Matches contents of a <td> tag (that optionally has other tags surrounding
# the desired contents).
# <td...> { matched contents } </td>
td_p = whitespace_p + '<td.*?>' + whitespace_p + '(.*?)' + \
    whitespace_p + '</td>' + whitespace_p

# Matches contents of between some number of html tags.
# (<?>, ...) { matched contents } (</?>, ...)
tag_contents_p = whitespace_p + greedy_html_tag_p + '([^<]*)' + \
    nongreedy_html_tag_p + whitespace_p

# Matches a URL; assumes that there are no other HTML attributes following it.
url_p = '(http://.*?)(?:")?>'

# Matches a course code. ABC123H1
course_code_p = '([A-Z]{3}\d{3}[HYhy]\d)'

# Matches lecture/tutorial section code; group 1 indicates Lecture or Tutorial,
# group 2 is the numeric code.
section_p = '(L|T)(\d{4})'

# Compile regex.
row_regex = re.compile(row_p, re.DOTALL)
cell_regex = re.compile(cell_p, re.DOTALL)
td_regex = re.compile(td_p, re.DOTALL)
tag_contents_regex = re.compile(tag_contents_p, re.DOTALL)
url_regex = re.compile(url_p)
course_code_regex = re.compile(course_code_p)
section_regex = re.compile(section_p)



# --- Functions ---

def insert_into_course_info(cursor, code, title, url):
    '''(Cursor, str, str, str) -> NoneType
    Insert a row into table course_info. Use cursor to contact the database.
    '''
    insert_stmt = 'INSERT INTO course_info VALUES (?, ?, ?)'
    cursor.execute(insert_stmt, (code, title, url))

def insert_into_course_sched(cursor, code, session, sec_type, sec_num, time,
                             loc, inst, subtitle):
    '''(Cursor, str, str, str, str, str, str, str, str) -> NoneType
    Insert a row into table course_sched. Use cursor to contact the database.
    '''
    insert_stmt = 'INSERT INTO course_sched VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
    cursor.execute(insert_stmt, (code, session, sec_type, sec_num, time,
                                 loc, inst, subtitle))

def build_url(code):
    '''(str) -> str
    Return the course calendar url for the course with course code "code".
    '''
    return 'http://www.artsandscience.utoronto.ca/ofr/calendar/crs_' + \
        code[0:3].lower() + '.htm#' + code

def build_first_year_seminar_url(code):
    '''(str) -> str
    Return the course calendar url for the first-year seminar with course code
    "code".
    '''
    return 'http://www.artsandscience.utoronto.ca/ofr/1314_199/' + \
        code.lower() + '.html'

def build_first_year_seminar_section_url(code, sec_type, sec_num, session):
    '''(str) -> str
    Return the course calendar url for the first-year seminar section with
    course code "code", section type "sec_type", section number "sec_num", and
    session "session".
    '''
    return build_first_year_seminar_url(code) + '#' + sec_type + \
           sec_num + session

def clean_string(text):
    '''(str) -> str
    Return text with trailing whitespace stripped, or "null" if text is the
    empty string or is "&nbsp;".
    '''
    text = text.strip()
    if text == "&nbsp;" or text == "":
        return "null"
    else:
        return text

def get_timetable_urls():
    '''(NoneType) -> list
    Return a list of URLs to each department's timetable pages.
    '''

    # Get HTML for the top-level list of all departments.
    dept_list_url = 'http://www.artsandscience.utoronto.ca/' + \
        'ofr/timetable/winter/sponsors.htm'
    dept_list_html = str(urlopen(dept_list_url).read())

    local_url_regex = re.compile('([a-z]*\.html)')
    all_urls = set(re.findall(local_url_regex, dept_list_html))

    # In meta declaration. If start search b/w <body>, won't have to
    # manually remove.
    all_urls.remove('text.html')

    # First year seminars. Cleaner HTML. Deal with separately.
    all_urls.remove('assem.html')

    # Architecture listings are on their own site.
    all_urls.remove('arc.html')

    all_urls = list(all_urls)
    for link in range(len(all_urls)):
        all_urls[link] = \
            'http://www.artsandscience.utoronto.ca/ofr/timetable/winter/' + \
            all_urls[link]
    return all_urls

def store_timetable(cursor, page_url):
    '''(Cursor, str) -> NoneType
    Parse the timetable HTML found at page_url, and add that data to the
    database using cursor.
    '''

    # Basic algorithm: Go through the rows one by one, and add the previous row
    # to the database. Don't add the current row immediately to the database,
    # because some course sections span two rows. If we inserted each row
    # immediately, we'd need to update the database on the second row. Database
    # actions are slow, so we shouldn't use two actions to insert one course
    # section.

    print('\n', page_url, sep='')

    code, session, sec_type, sec_num, time, loc, inst = \
        None, None, None, None, None, None, None
    subtitle = "null"  # Not processing subtitles for these courses for now.
    cancelled = False

    page_html = str(urlopen(page_url).read())

    # Contents of each row in table (i.e. <tr> </tr> guts) as a string.
    rows = re.findall(row_regex, page_html)

    for row in rows:
        cells = re.findall(cell_regex, row)
        if len(cells) >= 5:
            this_code = clean_string(cells[0])
            this_title = clean_string(cells[2])
            if this_code != 'null':  # If course code not 'null'....
                # This is the first row of a new course.

                if code != None:
                    # This is not the first row of the table since we've already
                    # got a course code from a previous row. So, insert that
                    # preceding row into the db.
                    if cancelled:
                        cancelled = False
                    else:
                        insert_into_course_sched( \
                            cursor, code, session, sec_type, sec_num, time,
                            loc, inst, subtitle)
                        print(code, session, sec_type, sec_num, time, loc,
                              inst, subtitle)

                if this_code != code:
                    # This course code is different from the previous course
                    # code, which means this is a completely different course
                    # (and not just the spring session of the same course).
                    # So, insert this course's title and url into course_info.
                    insert_into_course_info( \
                        cursor, this_code, this_title, build_url(this_code))
                    print("---", this_code, this_title, build_url(this_code))

                if re.search('(Cancel|cancel|CANCEL)', row) == None:
                    # Save everything in this row to vars. The section data here
                    # will be inserted into course_sched in the next loop iter.
                    code = this_code
                    session = clean_string(cells[1])
                    sec_type = cells[3][0]
                    sec_num = cells[3][1:5]
                    time = clean_string(cells[5])
                    loc = clean_string(cells[6])
                    inst = clean_string(cells[7])
                else:
                    # We DON'T want this section to be added on the next
                    # iteration b/c it has been cancelled! However, we do want
                    # to ensure that if this course has another open section
                    # in the next row(s), it will be able to get the correct
                    # course title/code/session.
                    code = this_code
                    session = clean_string(cells[1])
                    cancelled = True

            else:
                # This row is part of the course currently being processed.
                if re.search('(Cancel|cancel|CANCEL)', row) == None:
                    if cells[3][0] == "&":  # If sec_type == "&"
                        # Part of the previous row! This means this row's
                        # time should be added onto the previous row's time.
                        time += ", " + cells[5]

                        # This row's data is redundant with the previous row.
                        # We don't insert the previous row's data into the db
                        # now b/c the next loop iteration will do that.

                    else:
                        # This row is its own section. Insert the previous row
                        # into the db.
                        if cancelled:
                            # Don't insert anything into db. Reset flag.
                            cancelled = False
                        else:
                            insert_into_course_sched( \
                                cursor, code, session, sec_type, sec_num, time,
                                loc, inst, subtitle)
                            print(code, session, sec_type, sec_num, time, loc,
                                  inst, subtitle)

                        # Save all the unique data from this row and continue.
                        sec_type = cells[3][0]
                        sec_num = cells[3][1:]
                        time = clean_string(cells[5])
                        loc = clean_string(cells[6])
                        inst = clean_string(cells[7])

    # Insert the very last row of the table into the db.
    insert_into_course_sched(cursor, code, session, sec_type, sec_num, time,
                             loc, inst, subtitle)
    print(code, session, sec_type, sec_num, time, loc, inst, subtitle)

def store_timetables(cursor, dept_urls):
    '''(Cursor, list) -> NoneType
    Parse the pages at each of the urls in list dept_urls, and store their
    timetable data in the database using cursor. dept_urls is a list of strings.
    '''
    for url in dept_urls:
        store_timetable(cursor, url)

def store_first_year_seminars(cursor):
    '''(Cursor) -> NoneType
    Parse the page for first-year seminars and store the course data in the
    database using cursor. This page can't be processed using store_timetable
    because it has a different format.
    '''

    # Regex that processes the course heading rows specific to this page.
    course_info_p = course_code_p + '([F|S|Y])' + ':\s*([^:]*)'
    course_info_regex = re.compile(course_info_p)

    # Initializing temp vars. All sections on this page are of type "L".
    code = None
    sec_type = 'L'

    page_url = \
        'http://www.artsandscience.utoronto.ca/ofr/timetable/winter/assem.html'
    page_html = str(urlopen(page_url).read())
    rows = re.findall(row_regex, page_html)

    for row in rows:
        if re.search('(Cancel|cancel|CANCEL)', row) == None:
            # Get whatever's between the <td> tags.
            cells = re.findall(td_regex, row)
            if len(cells) == 1:
                # Remove any remaining tags.
                cells = re.findall(tag_contents_regex, cells[0])
                # If we can parse the contents of the cell into course info,
                # then this is a course heading.
                result = re.findall(course_info_regex, cells[0])
                if result != []:
                    this_code = result[0][0]
                    session = result[0][1]
                    title = clean_string(result[0][2])
                    if this_code != code:
                        # We haven't encountered another session of this course
                        # before, so we need to save its title/url info.
                        code = this_code
                        insert_into_course_info(cursor, \
                            code, title, build_first_year_seminar_url(code))
                        print("---", code, title,
                              build_first_year_seminar_url(code))

            else:
                # Remove any remaining tags. Yes, we need to do this separate
                # from the <td> tag stripping, b/c if we do it together, any
                # cells that are empty (i.e. don't even have "&nbsp;") will
                # be lost, and the cell structure this algorithm relies on will
                # crash and burn.
                for i in range(len(cells)):
                    cells[i] = re.findall(tag_contents_regex, cells[i])[0]

                # Check if the first cell can be parsed into a section code;
                # if it can, then this is a course section and not a table
                # heading.
                result = re.findall(section_regex, cells[0])
                if result != []:
                    # Remove any remaining tags.
                    sec_num = cells[0][1:5]
                    time = clean_string(cells[3])
                    loc = clean_string(cells[4])
                    inst = clean_string(cells[5])
                    subtitle = clean_string(cells[1])
                    # Right now, don't need to use section URLs.
                    #section_url = build_first_year_seminar_section_url(\
                        #code, sec_type, sec_num, session)

                    # Insert this seminar into course_sched.
                    insert_into_course_sched( \
                        cursor, code, session, sec_type, sec_num, time, loc,
                        inst, subtitle)
                    print(code, session, sec_type, sec_num, time, loc, inst,
                          subtitle)


if __name__ == '__main__':
    # TODO: Why were we storing the database in memory?

    # Get a connection to our db (which is a file called "ttgen.db").
    conn = sqlite3.connect('ttgen.db')

    # Get a cursor, which we'll use to query/update the db.
    cur = conn.cursor()

    # If the tables already exist, delete them so that we can rebuild them
    # from the ground up.
    cur.execute('DROP TABLE IF EXISTS course_info')
    cur.execute('DROP TABLE IF EXISTS course_sched')

    # Create a table that stores course titles and urls.
    create_course_info_stmt = 'CREATE TABLE course_info(' + \
        '_id INTEGER PRIMARY KEY AUTOINCREMENT, ' + \
        'code TEXT NOT NULL, ' + \
        'title TEXT NOT NULL, ' + \
        'url TEXT NOT NULL, ' + \
        'PRIMARY KEY(code))'
    cur.execute(create_course_info_stmt)

    # Create a table that stores course section information.
    create_course_sched_stmt = 'CREATE TABLE course_sched(' + \
        'code TEXT NOT NULL,' + \
        'session TEXT NOT NULL, ' + \
        'sec_type TEXT NOT NULL, ' + \
        'sec_num TEXT NOT NULL, ' + \
        'time TEXT NOT NULL, ' + \
        'loc TEXT, ' + \
        'inst TEXT, ' + \
        'subtitle TEXT, ' + \
        'PRIMARY KEY(code, session, sec_type, sec_num), ' + \
        'FOREIGN KEY(code) REFERENCES course_info(code))'
    cur.execute(create_course_sched_stmt)

    # Parse and store timetable data into the db.
    store_timetables(cur, get_timetable_urls())
    store_first_year_seminars(cur)

    '''
    cur.execute('SELECT * FROM course_sched')
    for i in cur:
        print (i)
    '''

    # Close cursor.
    cur.close()

    # Save changes to our db and close it.
    conn.commit()
    conn.close()
